package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.kit.ExcelExportUtil;
import cn.com.dashihui.kit.ExcelExportUtil.Pair;
import cn.com.dashihui.web.dao.Store;
import cn.com.dashihui.web.dao.StoreImages;

public class StoreTongService {

	public Record countStore(String sellerId) {
		return Db.findFirst("select A.tolnumber,B.sjshnumber,C.sjnumber,D.xjshnumber,E.xjnumber FROM"
				+ " (select  count(id) as tolnumber from t_dict_store where sellerId= ? ) A,(Select count(id) as sjshnumber from t_dict_store where state=1 and sellerId= ? ) B,"
				+ "(SELECT count(id) as sjnumber from t_dict_store where state=2 and sellerId= ? ) C,(select COUNT(id) as xjshnumber from t_dict_store where state =3 and sellerId= ? ) D,(select count(id) as xjnumber from t_dict_store where state=4 and sellerId= ? ) E",sellerId,sellerId,sellerId,sellerId,sellerId);
	}

	/**
	 * 根据公司userid查询分公司
	 */
	public List<Record> findAllStoreid(int userid) {
		return Db.find("select storeid,title from t_dict_store where sellerId=(select sellerId from t_bus_seller where userid=?)",userid);
	}
	
	public Page<Record> findByPage(int pageNum, int pageSize, String storeIds, String sStoreId,
			String beginDate, String endDate , String sellerId) {
		StringBuffer sBuffer = new StringBuffer(" FROM t_dict_store T "
				+ "inner join t_bus_seller A on T.sellerId=A.sellerId "
				+ "LEFT JOIN (SELECT TB.storeid, count(TA.id) AS countGoods FROM t_bus_goods TA INNER JOIN t_bus_goods_store_rel TB on TA.good_store=TB.goodstore GROUP BY TB.storeid) B on T.storeid=B.storeid"
				+ " LEFT JOIN (select TB.storeid,SUM(L.amount)as countMoney ,SUM(L.count)as countNum from t_bus_order_list L left join t_bus_order O on O.orderNum = L.orderNum left join t_bus_goods G on G.id = L.goodsid "
				+ "INNER JOIN t_bus_goods_store_rel TB on G.good_store=TB.goodstore where O.orderState  NOT in(1,5,7,9) AND G.groupbuying = 1  GROUP BY TB.storeid) C on T.storeid=C.storeid "
				+ " LEFT JOIN (select TB.storeid,SUM(O.amount)as countMoney ,SUM(O.countnum)as countNum from t_bus_order_gb O left join t_bus_goods G on G.id = O.goodsid "
				+ "INNER JOIN t_bus_goods_store_rel TB on G.good_store=TB.goodstore where O.orderState  NOT in(1,5,7,9) AND G.groupbuying = 2 GROUP BY TB.storeid) D on T.storeid=D.storeid "
				+ " LEFT JOIN (select storeid,SUM(countnum)as sellNum from t_bus_order where orderState!=2 and tuistate=2  GROUP BY storeid) K on T.storeid=K.storeid WHERE 1=1");
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" AND T.sellerId=?");
		params.add(sellerId);
	/*	if(!StrKit.isBlank(sellerId) && !"0".equals(sellerId)){
			sBuffer.append(" AND T.sellerId=?");
			params.add(sellerId);
		}*/
		if(!StrKit.isBlank(storeIds)&&!"0".equals(storeIds)){
			sBuffer.append(" AND T.storeid in ('"+storeIds+"')");
		}
	
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.createtime,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.createtime,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.createtime,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		sBuffer.append(" ORDER BY T.createtime DESC");
		return Db.paginate(pageNum, pageSize, "SELECT T.id,T.storeid,T.title,T.scope,A.sellerName,T.createtime,T.aname,T.aphone,T.checktime,T.qstate,T.state,ifnull(B.countGoods,0) countGoods,ifnull(C.countMoney,0) countMoney,ifnull(C.countNum,0) countNum,ifnull(D.countMoney,0) countMoneygb,ifnull(D.countNum,0) countNumgb,ifnull(K.sellNum,0) sellNum", sBuffer.toString(), params.toArray());
	}
/**
 * 销量统计
 * @param pageNum 
 * @param pageSize
 * @param storeIds
 * @param sellerId
 * @param sStoreId
 * @param beginDate
 * @param endDate
 * @param date
 * @return
 */
	public Page<Record> findXSByPage(int pageNum, int pageSize, String storeIds, String sellerId, String sStoreId,String beginDate, String endDate,int date) {
		StringBuffer sBuffer = new StringBuffer(" FROM t_dict_store T inner join t_bus_seller A on T.sellerId=A.sellerId LEFT JOIN "
				+ "(SELECT TB.storeid, count(TA.id) AS countGoods FROM t_bus_goods TA INNER JOIN t_bus_goods_store_rel TB on TA.good_store=TB.goodstore GROUP BY TB.storeid) B on T.storeid=B.storeid");
		switch (date) {
		case 1://当日
			sBuffer.append( " LEFT JOIN (select storeid,SUM(amount)as countMoney ,SUM(countnum)as countNum from t_bus_order where orderState NOT in(1,5,7,9)  and to_days(payDate) =to_days(now()) GROUP BY storeid) C on T.storeid=C.storeid WHERE 1=1");
			break;
		case 2://本周
			sBuffer.append( " LEFT JOIN (select storeid,SUM(amount)as countMoney ,SUM(countnum)as countNum from t_bus_order where orderState NOT in(1,5,7,9)  and YEARWEEK(date_format(payDate,'%Y-%m-%d'))= YEARWEEK(now()) GROUP BY storeid) C on T.storeid=C.storeid WHERE 1=1");
			break;
		case 3://本月
			sBuffer.append( " LEFT JOIN (select storeid,SUM(amount)as countMoney ,SUM(countnum)as countNum from t_bus_order where orderState NOT in(1,5,7,9)  and date_format(payDate,'%Y-%m')=date_format(now(),'%Y-%m') GROUP BY storeid) C on T.storeid=C.storeid WHERE 1=1");
			break;
		default:
			//默认全部
			sBuffer.append( " LEFT JOIN (select storeid,SUM(amount)as countMoney ,SUM(countnum)as countNum from t_bus_order where orderState NOT in(1,5,7,9) GROUP BY storeid) C on T.storeid=C.storeid WHERE 1=1");
			break;
		}
		
		List<Object> params = new ArrayList<Object>();
		if(!StrKit.isBlank(sellerId)&& !"0".equals(sellerId)){
			sBuffer.append(" AND T.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(sStoreId)&&!"0".equals(sStoreId)){
			sBuffer.append(" AND T.storeid in ('"+sStoreId+"')");
		}
		if(!StrKit.isBlank(storeIds) && !"0".equals(storeIds)){
			sBuffer.append(" AND T.storeid=?");
			params.add(storeIds);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.createtime,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.createtime,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.createtime,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		sBuffer.append(" ORDER BY countMoney desc,T.createtime DESC");
		return Db.paginate(pageNum, pageSize, "SELECT T.* , ifnull(B.countGoods,0) countGoods,ifnull(C.countMoney,0) countMoney,ifnull(C.countNum,0) countNum", sBuffer.toString(), params.toArray());
	}
	
	
	public Record detail(String storeid) {
		return Db.findFirst("select * from t_dict_store where storeid=?",storeid);
	}
	/**
	 * 分公司图片列表
	 * @param storeid
	 * @return
	 */
	public List<StoreImages> findAllImages(String stroeimagenum){
		return StoreImages.me().find("SELECT A.* FROM t_bus_store_images A WHERE A.stroeimagenum=? ORDER BY A.orderNo",stroeimagenum);
	}
	/**
	 * 导出数据
	 * @param response
	 * @param request
	 * @param listz 
	 * @param titles2 
	 * @param member
	 */
	public void export(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "分公司统计列表", titles, listz);
	}
	public List<Record> findExcelByPage(String storeIds, String sellerId, String sStoreId,String beginDate, String endDate,int date) {
		StringBuffer sBuffer = new StringBuffer("SELECT T.storeid,T.title,T.scope,A.sellerName,T.createtime,T.aname,T.aphone,T.checktime,T.qstate,T.state,ifnull(B.countGoods,0) countGoods,ifnull(C.countMoney,0) countMoney,ifnull(C.countNum,0) countNum FROM t_dict_store T inner join t_bus_seller A on T.sellerId=A.sellerId LEFT JOIN "
				+ "(SELECT TB.storeid, count(TA.id) AS countGoods FROM t_bus_goods TA INNER JOIN t_bus_goods_store_rel TB on TA.good_store=TB.goodstore GROUP BY TB.storeid) B on T.storeid=B.storeid");
		switch (date) {
		case 1://当日
			sBuffer.append( " LEFT JOIN (select storeid,SUM(amount)as countMoney ,SUM(countnum)as countNum from t_bus_order where orderState NOT in(1,5,7,9)  and to_days(payDate) =to_days(now()) GROUP BY storeid) C on T.storeid=C.storeid WHERE 1=1");
			break;
		case 2://本周
			sBuffer.append( " LEFT JOIN (select storeid,SUM(amount)as countMoney ,SUM(countnum)as countNum from t_bus_order where orderState NOT in(1,5,7,9)  and YEARWEEK(date_format(payDate,'%Y-%m-%d'))= YEARWEEK(now()) GROUP BY storeid) C on T.storeid=C.storeid WHERE 1=1");
			break;
		case 3://本月
			sBuffer.append( " LEFT JOIN (select storeid,SUM(amount)as countMoney ,SUM(countnum)as countNum from t_bus_order where orderState NOT in(1,5,7,9)  and date_format(payDate,'%Y-%m')=date_format(now(),'%Y-%m') GROUP BY storeid) C on T.storeid=C.storeid WHERE 1=1");
			break;
		default:
			//默认全部
			sBuffer.append( " LEFT JOIN (select storeid,SUM(amount)as countMoney ,SUM(countnum)as countNum from t_bus_order where orderState NOT in(1,5,7,9)  GROUP BY storeid) C on T.storeid=C.storeid WHERE 1=1");
			break;
		}
		
		List<Object> params = new ArrayList<Object>();
		if(!StrKit.isBlank(sellerId)&& !"0".equals(sellerId)){
			sBuffer.append(" AND T.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(sStoreId)&&!"0".equals(sStoreId)){
			sBuffer.append(" AND T.storeid in ('"+sStoreId+"')");
		}
		if(!StrKit.isBlank(storeIds) && !"0".equals(storeIds)){
			sBuffer.append(" AND T.storeid=?");
			params.add(storeIds);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND T.createtime BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.createtime,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.createtime,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		sBuffer.append(" ORDER BY countMoney desc,T.createtime DESC");
		return Db.find(sBuffer.toString(), params.toArray());
	}
	
	/**
	 * 从对象中获取公司sellerid
	 */
	public Record FindById(int userId) {
		return Db.findFirst("select * from t_bus_seller where userId=?",userId);
	}
	public Record findByUserId(int userid){
		return Db.findFirst("select * from t_bus_seller where userid=?",userid);
	}
	/**
	 * 查询分公司的状态是否通过
	 * @return
	 */
	public Record findStoreState(int userid) {
		// TODO Auto-generated method stub
		return Db.findFirst("SELECT  * from  t_dict_store as A inner JOIN t_auth_admin_seller as B ON A.storeid=B.storeid where B.id=?",userid);
		
	}
	/**
	 * 根据userid,获取角色
	 * @param userid
	 * @return
	 */
	public int findByRoleId(int userid){
		return Db.queryInt("SELECT C.id  from t_auth_admin_seller as A inner JOIN  t_auth_admin_roles_seller as B ON A.id=B.userid  INNER JOIN t_auth_roles_seller AS C ON B.roleid = C.id where userid=?" ,userid);
	}
	
	
	public Record findById(String storeid) {
		return Db.findFirst("select storeid,title,aname,aphone,qstate from t_dict_store where storeid=?",storeid);
	}

	public Store findByStoreId(String storeid) {
		return Store.me().findFirst("select * from t_dict_store where storeid=?",storeid);
	}

	public boolean edit(Store store) {
		return store.update();
	}
	/**
	 *查找出当前商品的订单信息
	 */
	public Page<Record> findOrderById(int pageNum,int pageSize,String storeid){
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" FROM t_bus_order_list LI "
				+ "LEFT JOIN t_bus_order A ON LI.orderNum = A.orderNum "
				+ "LEFT JOIN t_bus_goods AS G ON  G.id=LI.goodsid "
				+ "LEFT JOIN t_bus_goods_store_rel H on H.goodstore = G.good_store "
				+ "LEFT JOIN t_dict_store B ON H.storeid = B.storeid "
				+ "LEFT JOIN t_bus_seller C ON B.sellerId=C.sellerId "
				+ "LEFT JOIN t_bus_order_toker_user_log L ON A.orderNum=L.orderNum "
				+ "LEFT JOIN t_bus_user F ON A.userid=F.id "
				+ " WHERE 1=1 ");
		if (StrKit.notBlank(storeid)) {
			sBuffer.append(" AND B.storeid=? ");	
			params.add(storeid);	
		}
		sBuffer.append(" AND A.orderState!=5");
		sBuffer.append(" AND A.orderState!=1");
		sBuffer.append(" AND A.orderState!=7");
		sBuffer.append(" AND A.orderState!=9");
		sBuffer.append(" AND G.groupbuying = 1 ");
		sBuffer.append(" ORDER BY A.createDate DESC");
		
		Page<Record> pagee = Db.paginate(pageNum,pageSize,"SELECT A.*,F.id as uid,F.name,F.card,B.title as xdstore ",sBuffer.toString(),params.toArray());
		return pagee;
	}
	/**
	 *查找出团购订单信息
	 */
	public Page<Record> findOrderByIdgb(int pageNum,int pageSize,String storeid){
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" FROM t_bus_order_gb A "
				+ "left JOIN t_bus_goods AS G ON  G.id=A.goodsid "
				+ "LEFT JOIN t_bus_goods_store_rel H on H.goodstore = G.good_store "
				+ "LEFT JOIN t_dict_store B ON H.storeid = B.storeid "
				+ "LEFT JOIN t_bus_seller C ON B.sellerId=C.sellerId "
				+ "LEFT JOIN t_bus_order_toker_user_log L ON A.orderNum=L.orderNum "
				+ "LEFT JOIN t_bus_user F ON A.userid=F.id "
				+ "LEFT JOIN t_dict_store E ON A.dhstoreid = E.storeid WHERE 1=1 ");
		if (StrKit.notBlank(storeid)) {
			sBuffer.append(" AND B.storeid=? ");	
			params.add(storeid);	
		}
		sBuffer.append(" AND A.orderState!=5");
		sBuffer.append(" AND A.orderState!=1");
		sBuffer.append(" AND A.orderState!=7");
		sBuffer.append(" AND A.orderState!=9");
		sBuffer.append(" AND G.groupbuying = 2 ");
		sBuffer.append(" ORDER BY A.createDate DESC");
		Page<Record> pagee = Db.paginate(pageNum,pageSize,"SELECT A.*,F.id as uid,F.name,F.card ,B.title as xdstore ",sBuffer.toString(),params.toArray());
		return pagee;
	}
}
